package jsyf.com.util;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by y on 2018/8/9.
 */
public class ExportWeekExcel {
    private final Logger logger = LoggerFactory.getLogger(ExportExcel.class);
    //显示的导出表的标题
    private String title;
    //导出表的列名

    private List<JSONObject> models = new ArrayList<JSONObject>();
    private JSONObject yuce = new JSONObject();
    private HttpServletResponse  response;

    //构造方法，传入要导出的数据
    public ExportWeekExcel(String title,  List<JSONObject> models,JSONObject yuce,HttpServletResponse  response) {
        this.models = models;
        this.title = title;
        this.response = response;
        this.yuce=yuce;
    }
    /*{
        "companyId": "2017121617484B3A8193DB9B6CB372AC",
            "date": "2018-08-09",
            "regionId": "20171218123745DFB26D65BF15D0D72E",
            "userId": "2017121620474C9EB1EED87A77DDF69D"
    }*/
    public  void creat2003Excel() throws FileNotFoundException, IOException {
        HSSFWorkbook workBook = new HSSFWorkbook();// 创建 一个excel文档对象
        HSSFSheet sheet = workBook.createSheet();// 创建一个工作薄对象
        // sheet.setColumnWidth(1, 10000);// 设置第二列的宽度为
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int beginMonth = Integer.parseInt(sdf.format(new Date()).split("-")[1]);
        int middleMonth=(beginMonth + 1) % 12 == 0 ? 12 : (beginMonth + 1) % 12;
        int endMonth= (beginMonth + 2) % 12 == 0 ? 12 : (beginMonth + 2) % 12;
        HSSFCellStyle style = workBook.createCellStyle();// 创建样式对象
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中
        // 设置字体
        //HSSFFont font = workBook.createFont();// 创建字体对象
        //font.setFontHeightInPoints((short) 15);// 设置字体大小
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体
        //font.setFontName("黑体");// 设置为黑体字
        //style.setFont(font);// 将字体加入到样式对象
        // 设置对齐方式
        //
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
        // 设置边框
        //style.setBorderTop(HSSFCellStyle.BORDER_THICK);// 顶部边框粗线
        //style.setTopBorderColor(HSSFColor.RED.index);// 设置为红色
            for(int i=0;i<models.size();i++){
                HSSFRow row=sheet.createRow(i);
                for(int j=0;j<4;j++){
                if(!StringUtils.isBlank(models.get(i).getString("companyName"))&&StringUtils.isBlank(models.get(i).getString("modelName"))){
                    row.setHeightInPoints(23);
                    HSSFCellStyle style1 = workBook.createCellStyle();// 创建样式对象
                    style1.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中
                    style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
                    style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
                    style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                    style1.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
                    HSSFCell cell = row.createCell(1);
                    cell.setCellValue(title);
                    cell.setCellStyle(style1);
                }
                else{
                    HSSFCell cell = row.createCell(j);// 创建单元格
                    switch (j){
                        case 0:
                            cell.setCellValue(models.get(i).getString("modelName"));
                            break;
                        case 1:cell.setCellValue(models.get(i).getString("firstMonth"));
                            if("机型".equals(models.get(i).getString("modelName"))){
                              cell.setCellValue(models.get(i).getString("firstMonth")+"月");
                              }

                            break;
                        case 2: cell.setCellValue(models.get(i).getString("secondMonth"));
                            if("机型".equals(models.get(i).getString("modelName"))){
                                cell.setCellValue(models.get(i).getString("secondMonth")+"月");
                            }
                            break;
                        case 3: cell.setCellValue(models.get(i).getString("thirdMonth"));
                            if("机型".equals(models.get(i).getString("modelName"))){
                                cell.setCellValue(models.get(i).getString("thirdMonth")+"月");
                            }
                            break;
                    }
                    style.setFillPattern(FillPatternType.NO_FILL);
                    cell.setCellStyle(style);
                }
                }
            }
//尾部
        for(int i=0;i<4;i++){
            HSSFRow row=sheet.createRow(models.size()+i);
            for(int j=0;j<4;j++){
                HSSFCell cell = row.createCell(j);
                switch (j){
                    case 0:
                        if(i==0){
                            cell.setCellValue("本周预测");
                        }else if(i==1){
                            cell.setCellValue("上周预测");
                        }else if(i==2){
                            cell.setCellValue("差距");
                        }else{
                            cell.setCellValue("实际销售台数");
                        }
                        break;
                    case 1:
                        if(i==0){
                        cell.setCellValue(yuce.getString("firstMonthYuce"));
                        }else if(i==1){
                        cell.setCellValue(yuce.getString("firstMonthBef"));
                        }else if(i==2){
                        cell.setCellValue(yuce.getString("firstMonthSub"));
                        }else{
                            cell.setCellValue(yuce.getString("actualSaleRemark"));
                        }
                        break;
                    case 2:
                        if(i==0){
                            cell.setCellValue(yuce.getString("secondMonthYuce"));
                        }else if(i==1){
                            cell.setCellValue(yuce.getString("secondMonthBef"));
                        }else if(i==2){
                            cell.setCellValue(yuce.getString("secondMonthSub"));
                        }
                        break;
                    case 3:
                        if(i==0){
                            cell.setCellValue(yuce.getString("thirdMonthYuce"));
                        }else if(i==1){
                            cell.setCellValue(yuce.getString("thirdMonthBef"));
                        }else if(i==2){
                            cell.setCellValue(yuce.getString("thirdMonthSub"));
                        }
                        break;
                }
                style.setFillPattern(FillPatternType.NO_FILL);
                cell.setCellStyle(style);
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 3));
        sheet.addMergedRegion(new CellRangeAddress(models.size()+3, models.size()+3, 1, 3));
        // 格式化日期
        //style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        //cell.setCellValue(new Date());// 写入当前日期
        //cell.setCellStyle(style);// 应用样式对象
        // 文件输出流
        // excel 表文件名
        if(workBook!=null){
            try
            {
                // excel 表文件名
                String fileName = title + ".xls";
                String fileName11 = URLEncoder.encode(fileName,"UTF-8");
                String headStr = "attachment; filename=\"" + fileName11 + "\"";
                response.setContentType("APPLICATION/OCTET-STREAM");
                response.setHeader("Content-Disposition", headStr);
                OutputStream out = response.getOutputStream();
                workBook.write(out);
                out.flush();
                out.close();
            }
            catch (IOException e)
            {
                e.printStackTrace();
            }

        }
       /*FileOutputStream os = new FileOutputStream("甘肃预测.xls");
       workBook.write(os);// 将文档对象写入文件输出流
        os.close();// 关闭文件输出流
        System.out.println("创建成功 office 2003 excel");*/
    }


    /**
     * 对外提供读取excel 的方法
     */
}
